﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Reflection;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml.XPath;
using System.Xml;
using System.Data;
using System.IO;
using System.Resources;
using System.Runtime.Serialization.Formatters.Binary;
using Npgsql;
using SpectationClient.Stuff;
using SpectationClient.DataBaseDescription;
using System.Windows.Forms;

using SpectationClient.SQLCommandBuilder;

namespace SpectationClient
{
    public class DBManager {
        /// <summary>
        /// The connection to the SPECLIB Database
        /// </summary>
        private NpgsqlConnection con = null;

        
        /// <summary>
        /// Table Meta Infos of SPECLIB DataBase
        /// </summary>
        private DataBaseInfo  DBINFO = null;
        
        /// <summary>
        /// DataTable containing the SPECLIBs columnSRID & Spatial Refernence Systems (PostGIS)
        /// </summary>
        private DataTable GEOCOORD_SYSTEMS_SRIDTABLE = null;
       
        public DBManager(NpgsqlConnection con) {
            this.con = con.Clone();
            initialise();
            
        }

        ~DBManager() {
            this.con.Close();
        }


        
        public NpgsqlConnection Connection{
            get{return this.con;}
        }
        
        public DataRow getCOORD_SYS_INFO(long srid) {
            foreach(DataColumn c in this.GEOCOORD_SYSTEMS_SRIDTABLE.Columns) Console.WriteLine(c.DataType);
            
            foreach (DataRow r in this.GEOCOORD_SYSTEMS_SRIDTABLE.Rows) {
                if (Convert.ToInt64(r["SRID"]) == srid) return r;
            }
            return null;
        }

        public DataTable getSRIDTable() {
            return this.GEOCOORD_SYSTEMS_SRIDTABLE;
        }


        public void initialise() {
            
            //Check if folder with conf files exists...
            string pathResources = Application.StartupPath + @"\Resources\";
            
            //INIT Coordinate-Systems
            string pathSRIDXML = pathResources + Properties.Settings.Default.FN_SRID_TABLES_XML;
            string pathSPECTATIONXML = pathResources + Properties.Settings.Default.FN_DB_INFO_XML;

            if(!File.Exists(pathSPECTATIONXML)) {
                throw new Exception(String.Format("File {0} does not exists", pathSRIDXML));
            }

            //Read DBINFO
            SPECTATION_INFO_Reader SIR = new SPECTATION_INFO_Reader(this.con, pathSPECTATIONXML);
            this.DBINFO = SIR.readDBINFO();

            if(!File.Exists(pathSRIDXML)) {
                
           
                DataTable GEOCOORD_SYSTEMS = new DataTable();
                NpgsqlCommand cmd = new NpgsqlCommand(
                String.Format("SELECT * FROM \"{0}\".\"{1}\"", "public", "spatial_ref_sys"),this.Connection);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                da.Fill(GEOCOORD_SYSTEMS);

                this.GEOCOORD_SYSTEMS_SRIDTABLE = new DataTable("SRID_TABLE");
                this.GEOCOORD_SYSTEMS_SRIDTABLE.Columns.Add("columnSRID", typeof(int));
                this.GEOCOORD_SYSTEMS_SRIDTABLE.Columns.Add("NAME", typeof(String));
                this.GEOCOORD_SYSTEMS_SRIDTABLE.Columns.Add("UNIT", typeof(String));
                this.GEOCOORD_SYSTEMS_SRIDTABLE.Columns.Add("TYPE", typeof(String));
                //List<ICoordinateSystem> csl = getCoordinateSystems();
                //foreach (ICoordinateSystem i in csl) {
                Regex rGCS = new Regex("^GEOGCS");
                Regex rCCS = new Regex("^GEOCCS");
                Regex rPCS = new Regex("^PROJCS");
                Regex rUNIT = new Regex("UNIT.\"[^\"]+\"");
                Regex rNAME = new Regex("(?<=^(GEOGCS|GEOCCS|PROJCS).\")[^\"]*");
                Regex rQUOTE = new Regex("(?<=\")[^\"]*");


                foreach(DataRow r in GEOCOORD_SYSTEMS.Rows) {
                    //try {
                    DataRow dr = this.GEOCOORD_SYSTEMS_SRIDTABLE.NewRow();
                    String wkt = r["srtext"].ToString();
                    MatchCollection mcUNIT = rUNIT.Matches(wkt);
                    String strUNIT = rQUOTE.Match(mcUNIT[mcUNIT.Count - 1].Value).Value;
                    String strNAME = rNAME.Match(wkt).Value;
                    dr["UNIT"] = strUNIT;
                    dr["NAME"] = strNAME;
                    dr["columnSRID"] = r["columnSRID"];
                    Type t = null;
                    if(rPCS.IsMatch(wkt)) {

                        t = typeof(GeoAPI.CoordinateSystems.IProjectedCoordinateSystem);
                    } else if(rGCS.IsMatch(wkt)) {
                        t = typeof(GeoAPI.CoordinateSystems.IGeographicCoordinateSystem);
                    } else if(rCCS.IsMatch(wkt)) {
                        t = typeof(GeoAPI.CoordinateSystems.IGeocentricCoordinateSystem);
                    } else {
                        continue;
                    }
                    dr["TYPE"] = t.Name;
                    this.GEOCOORD_SYSTEMS_SRIDTABLE.Rows.Add(dr);
                }
                GEOCOORD_SYSTEMS_SRIDTABLE.WriteXml(pathSRIDXML, XmlWriteMode.WriteSchema);
            } else {
                GEOCOORD_SYSTEMS_SRIDTABLE = new DataTable();
                GEOCOORD_SYSTEMS_SRIDTABLE.ReadXml(pathSRIDXML);
            }
        }

               public DataTable getEmptyTable(String schema, String table) {
            return this.DBINFO[schema][table].getEmptyTable();
        }

        public DataBaseInfo getDataBaseInfo(){
            return DBINFO;
        }

        public SchemaInfo getSchemaInfo(String schema){
            return DBINFO[schema];
        }
        public TableInfo getTableInfo(String schema, String table) {
            return DBINFO[schema][table];
        }

        
        public ColumnInfo getColumnInfo(String schema, String table, String column) {
            return DBINFO[schema][table][column];
        }
        public List<String> getSchemaNames() {
            List<String> n = new List<string>(); 
            foreach(String s in DBINFO.Keys){n.Add(s);}
            return n ;
        }
        public List<String> getTableNames(String schema) {
            List<String> n = new List<string>();
            foreach (String s in DBINFO[schema].Keys) { n.Add(s); }
            return n;
        }

        /// <summary>
        /// This function tries to parse the input string SCHEMANAME.TABLENAME and to return both names seperately.
        /// </summary>
        /// <param name="input"></param>
        /// <param name="Schema"></param>
        /// <param name="tableName"></param>
        /// <returns>True if input string matches the pattern SCHEMANAME.TABLENAME and the tableName exists within speclib</returns>
        public bool TryParseSchemaTableNames(String input, out String schema, out String table) {
            bool ret = false;
            schema = table = null;
            Regex is_correct = new Regex("^[^.]+[.][^.]+$");
            if(!is_correct.IsMatch(input)) return false;
            Regex r_Schema = new Regex("^[^.]");
            Regex r_Table  = new Regex("[^.]+$");
            schema = r_Schema.Match(input).Value;
            table = r_Table.Match(input).Value;

            ret = DBINFO.ContainsKey(schema) && this.getSchemaInfo(schema).ContainsKey(table); 
            return ret;
        }

        /*
        public DataTable getMAP_Table(String schema, String table,String valuemember, String displaymember, bool firstempty) {
            return getMAP_Table(schema, table, valuemember, displaymember, null, firstempty);
        }
        */


        /// <summary>
        /// Returns a MAP DataTable that consists of two columns:
        /// Column "VALUE" has the value member (e.g. the first ID)
        /// Column "DISPLAY"  has informations (String) as given in the SchemaInfo
        /// </summary>
        /// <param name="Schema"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /*
        public DataTable getMAP_Table(String schema, String table, String valuemember, String displaymember, List<String> displayedColumns, bool firstempty) {
            TableInfo ti = this.getTableInfo(schema, table);
            DataTable dTemp = new DataTable(); //Temporary DT
            DataTable dFinal = new DataTable(table);//Persistent DT with two colums
            dFinal.Columns.Add(valuemember, ti.PrimaryKey[0].ValueType);
            dFinal.Columns.Add(displaymember, typeof(String));
            
            NpgsqlDataAdapter da = new NpgsqlDataAdapter();

            List<String> selectedColumns = new List<string>();
            selectedColumns.Add(ti.PrimaryKey[0].Name);
            if(displayedColumns == null) {
               displayedColumns = ti.Keys.ToList<String>();
            } 

            String colStr = TextHelper.combine(selectedColumns,"\"{0}\"", ",");
            da.SelectCommand.CommandText = String.Format("SELECT {0} FROM {1}", colStr, ti.SchemaTableName);
            da.SelectCommand.Connection = con;
            da.Fill(dTemp);
            con.Close();
            //columnValues.Columns.Add("NOTES", typeof(String));
            if (firstempty) {
                DataRow iPoint = dFinal.NewRow();
                iPoint[valuemember] = DBNull.Value;
                iPoint[displaymember] = " ";
                dFinal.Rows.Add(iPoint);
            }

            foreach(DataRow rowTemp in dTemp.Rows) {
                DataRow newRow = dFinal.NewRow();
                String displayString = TextHelper.combine(rowTemp, displayedColumns.ToArray(), " ");
                newRow[valuemember] = rowTemp[ti.PrimaryKey[0].Name];
                newRow[displaymember] = String.Format("[{0}] {1}", rowTemp[ti.PrimaryKey[0].Name],  displayString);
                dFinal.Rows.Add(newRow);
            }

            return dFinal;
        }
        */

        /// <summary>
        /// Extracts an embedded file out of a given assembly.
        /// </summary>
        /// <param name="assemblyName">The namespace of you assembly.</param>
        /// <param name="fileName">The name of the file to extract.</param>
        /// <returns>A stream containing the file data.</returns>
        public static Stream GetEmbeddedFile(string assemblyName, string fileName) {
            try {
                System.Reflection.Assembly a = System.Reflection.Assembly.Load(assemblyName); 
                String[] t1 = a.GetManifestResourceNames();
                Stream str = a.GetManifestResourceStream(assemblyName + "." + fileName);
                if (str == null)
                    throw new Exception("Could not locate embedded resource '" + fileName + "' in assembly '" + assemblyName + "'");
                return str;
            } catch (Exception e) {
                throw new Exception(assemblyName + ": " + e.Message);
            }
        }

        public static Stream GetEmbeddedFile(System.Reflection.Assembly assembly, string fileName){
            string assemblyName = assembly.GetName().Name;
            return GetEmbeddedFile(assemblyName, fileName);
        }

        public static Stream GetEmbeddedFile(Type type, string fileName){
            string assemblyName = type.Assembly.GetName().Name;
            return GetEmbeddedFile(assemblyName, fileName);
        }

       
    }
}
